package org.mbds.android.sismapp.db;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import org.mbds.android.sismapp.config.Properties;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SensorsDataSQLiteDatabase extends SQLiteOpenHelper
{
	public final static String TABLE_DATA = "data";
	public final static String TABLE_ACTIVITY_DATA = "activity_data";
	public final static String COL_ID = "_id";
	public final static String COL_DELAY = "delay";
	public final static String COL_DEVICE = "device";
	public final static String COL_SENSOR = "sensor";
	public final static String COL_ACTIVITY = "activity";
	public final static String COL_VALUE = "value";
	public final static String COL_X_MAX_MAX = "x_max_max";
	public final static String COL_X_MAX_MIN = "x_max_min";
	public final static String COL_Y_MAX_MAX = "y_max_max";
	public final static String COL_Y_MAX_MIN = "y_max_min";
	public final static String COL_Z_MAX_MAX = "z_max_max";
	public final static String COL_Z_MAX_MIN = "z_max_min";
	public final static String COL_NB_PEACS_MAX = "nb_peacs_max";
	public final static String COL_NB_PEACS_MIN = "nb_peacs_min";
	public final static String COL_X = "x";
	public final static String COL_Y = "y";
	public final static String COL_Z = "z";
	public final static String COL_DELTA_TIME = "delta_time";
	public final static String COL_DATE = "activity_date";
	
	

	public final static String[] FIELDS_DATA = {COL_ID, COL_DEVICE, COL_DELAY, COL_SENSOR, COL_DELTA_TIME, COL_X, COL_Y, COL_Z};
	// Define if the fields are used or not, add missing constants if needed...
	public final static String[] FIELDS_ACTIVITY_DATA = {COL_ID, COL_DEVICE, COL_ACTIVITY, COL_SENSOR, COL_NB_PEACS_MAX, COL_NB_PEACS_MIN, COL_X_MAX_MAX, COL_Y_MAX_MAX, COL_Z_MAX_MAX, COL_X_MAX_MIN, COL_Y_MAX_MIN, COL_Z_MAX_MIN};
	
	// Get all activity data found for a given device, delay and activity
	public final static String SQL_SELECT_ACTIVITY_DATA = "Select * from activity_data "
			+" where device='%DEVICE%' and delay=%DELAY% "
			+" and activity='%ACTIVITY%' ";
	// Insert new values in collected data table
	public final static String SQL_INSERT_DATA = "Insert into data ("+COL_DATE+","+COL_DEVICE+", "+COL_DELAY+", "+COL_SENSOR
			+", "+COL_DELTA_TIME+", "+COL_X+", "+COL_Y+", "+COL_Z+") "
			+" values('%DATE%','%DEVICE%', %DELAY%, '%SENSOR%', %DELTATIME%, %X%, %Y%, %Z%)";
	// Delete record from data
	public final static String SQL_DELETE_DATA = "Delete from data where _id=%ID%";
	
	//public final static String SQL_SELECT_TIME_Y = "Select " + COL_Y + " from " +TABLE_DATA + "where activity_date='2013_04_23_13:14:19'";
	public final static String SQL_SELECT_TIME_Y = "Select y from data where date = '2013_04_23_15:27:17'" ;
	
	//singleton/ single instance reference of database instance
	private static SensorsDataSQLiteDatabase _dbHelper;
	private final Context _context;
	private String _dbFile;
	private static String _dbPath;
	private boolean isInitialized = false;
    private static SQLiteDatabase checkDB = null;

   private SensorsDataSQLiteDatabase(Context context, String fileName)
   {
       super(context, fileName, null, 1);
       this._context = context;
       this._dbFile = fileName;
       this._dbPath = Properties.PATH+"/"+_dbFile;
       try {
    	   createDataBase();
		} catch (IOException e) {
			e.printStackTrace();
		}
   }
   
   public static boolean isDatabaseFound() {
	   return checkDB!=null;
   }
   
   public static SensorsDataSQLiteDatabase getInstance(Context appContext, String fileName)
   {
       if(_dbHelper == null)
       {
           _dbHelper = new SensorsDataSQLiteDatabase(appContext, fileName);
       }
       if (!_dbHelper.isInitialized)
    	   return null;
       return _dbHelper;
   }

   public synchronized void createDataBase() throws IOException
   {
       boolean dbExist = checkDataBase()!=null;

       if(!dbExist) {
    	   // Try to copy from assets
           try {
               copyDataBase();
           } catch (IOException e) {
        	   e.printStackTrace();
               throw new Error("Error copying database");
           }
       }
       isInitialized = true;
   }
   
   private static SQLiteDatabase checkDataBase()
   {
	   if (checkDB==null || !checkDB.isOpen())
       try
       {
           checkDB = SQLiteDatabase.openDatabase(_dbPath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
       }catch(Exception e)
       {
      		e.printStackTrace();
       }
       return checkDB;
   }
   private void copyDataBase() throws IOException
   {
       //Open your local db as the input stream
       InputStream myInput = _context.getAssets().open(_dbFile);
       // Path to the just created empty db
       String outFileName = _dbPath;
       //Open the empty db as the output stream
       OutputStream myOutput = new FileOutputStream(outFileName);
       //transfer bytes from the inputfile to the outputfile
       byte[] buffer = new byte[1024];
       int length;
       while ((length = myInput.read(buffer))>0)
       {
           myOutput.write(buffer, 0, length);
       }
      // retrieveDataFromDB();
       
       //Close the streams
       myOutput.flush();
       myOutput.close();
       myInput.close();
   }

   // Provide an SQL Statement and get a cursor containing the data
   public static Cursor query(String sql)
   {
       try
       {
           //open database to query
			SQLiteDatabase db = checkDataBase();
			if (db==null)
				return null;
//			db.setForeignKeyConstraintsEnabled(true);
			Cursor cursor = db.rawQuery(sql, null); 
			return cursor;
       }
       catch(Exception ex)
       {
           System.out.println("IndoorDatabase.query()- : ex " + ex.getClass() +", "+ ex.getMessage());
           return null;
       }
   }
   // Provide an insert, delete or update SQL and execute statement
	public static boolean executeSQLStatment(String sqlStatement) {
		SQLiteDatabase db = null;
		boolean ok = false;
		if (checkDB==null)
			return false;
		try {
			db = checkDB;
			// Create sim record
			db.execSQL(sqlStatement);
			ok = true;
		} catch (Exception e) {
		} finally {
		}
		
		return ok;
	}
  
	@Override
	public void onCreate(SQLiteDatabase arg0) {
		// TODO Auto-generated method stub
		
	}
	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub
	}
	

}